package com.intrasoft.nelsonalva.intrasms;

import java.io.FileNotFoundException;
import java.sql.SQLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.text.SimpleDateFormat;
import java.util.Date;

public class SelectQuery {

    public boolean querySubstring(String query) {

        boolean select = false;
        query = query.substring(0, 6);

        if (query.equalsIgnoreCase("select")) {

            select = true;
        } else {
            select = false;
        }
        return select;
    }

    public void writeExcel(ResultSet resultset, String sc) throws FileNotFoundException, SQLException, IOException, ClassNotFoundException, InvalidFormatException {


        String fechahora = fechaHora("ddMMMyyyy_HH.mm,ss");

        Workbook wb = new XSSFWorkbook();
        FileOutputStream fos = new FileOutputStream("Outputs/Output-" + sc + "_" + fechahora + ".xlsx");
        Sheet sh = wb.createSheet("Resultado");
        int columns = resultset.getMetaData().getColumnCount();

        Row tags = sh.createRow((short) 0);

        for (int i = 1; i <= columns; i++) {

            String columnname = resultset.getMetaData().getColumnName(i);
            Cell cell = tags.createCell((short) i - 1);
            cell.setCellValue(columnname);
        }

        int j = 1;
        String value;
        while (resultset.next()) {

            Row row = sh.createRow((short) j);

            for (int k = 1; k <= columns; k++) {

                value = resultset.getString(k);
                Cell cell = row.createCell((short) k - 1);
                cell.setCellValue(value);
            }
            j++;
        }
        System.out.println(sc + ", Registros guardados: " + (j - 1));
        wb.write(fos);
        fos.close();
    }

    public String fechaHora(String format) {
        Date d = new Date();
        SimpleDateFormat sdfs = new SimpleDateFormat(format);

        String fechaHora = sdfs.format(d);


        return fechaHora;
    }
}